package com.jinmdz.fmis.dao.dao.provider;

import com.jinmdz.fmis.dao.base.BaseProvider;
import com.jinmdz.fmis.dao.model.search.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.util.StringUtils;

import java.util.ArrayList;

/**
 * 殡仪馆业务查询
 *
 * @author GuanKui
 * @date 2020/2/24 9:54
 */
public class SearchProvider extends BaseProvider {

    /**
     * 火化档案（逝者）信息查询
     *
     * @param data 查询实体
     * @return
     * @author GuanKui
     * @date 2020/2/24  15:11
     */
    public String listCremationArchives(@Param("data") SearchCremationArchivesData data) {

        return new SQL() {
            {
                StringBuilder builder = new StringBuilder();
                builder.append(" funeral_dead.business_code,dead_name,dead_gender_code,dead_age,funeral_dead.certificate_no");
                builder.append(" ,transport_place,arrived_time,benefit_district_code");
                builder.append(" ,family_member.member_name,family_agent.agent_unit_name as agent_unit,family_agent.agent_name");
                builder.append(" ,cremation.cremation_no ");
                SELECT(builder.toString());
                FROM(" funeral_dead ");
                JOIN(" family_member on family_member.business_code = funeral_dead.business_code ");
                LEFT_OUTER_JOIN(" family_agent on family_agent.business_code = funeral_dead.business_code and family_agent.deleted = 0  ");
                LEFT_OUTER_JOIN(" cremation on cremation.business_code = funeral_dead.business_code and cremation.deleted = 0 and cremation.as_active = 1");
                WHERE(" funeral_dead.deleted = 0 and family_member.deleted = 0 ");

                // 业务编码
                if (valid(data.getBusinessCode())) {
                    WHERE(" funeral_dead.business_code like concat('%',#{data.businessCode},'%') ");
                }

                // 逝者姓名
                if (valid(data.getDeadName())) {
                    WHERE(" funeral_dead.dead_name like concat('%',#{data.deadName},'%') ");
                }

                // 家属姓名
                if (valid(data.getMemberName())) {
                    WHERE(" family_member.member_name like concat('%',#{data.memberName},'%') ");
                }

                // 承办人或单位
                if (valid(data.getAgentName())) {
                    WHERE(" ( family_agent.agent_unit_name like concat('%',#{data.agentName},'%') or family_agent.agent_name like concat('%',#{data.agentName},'%') )");
                }

                // 到馆时间
                if (data.getArrivedTimeStart() != null) {
                    WHERE(" DATE(funeral_dead.arrived_time) >= #{data.arrivedTimeStart,jdbcType=DATE} ");
                }

                // 到馆时间
                if (data.getArrivedTimeEnd() != null) {
                    WHERE(" DATE(funeral_dead.arrived_time) <= #{data.arrivedTimeEnd,jdbcType=DATE} ");
                }

                ORDER_BY("  funeral_dead.business_code desc ");
            }
        }.toString();
    }

    /**
     * 冷藏业务查询
     *
     * @param data 查询实体
     * @return
     * @author GuanKui
     * @date 2020/2/24  15:11
     */
    public String listColdStorage(@Param("data") SearchColdStorageData data) {
        return new SQL() {
            {
                StringBuilder builder = new StringBuilder();
                builder.append(" funeral_dead.business_code,dead_name,dead_gender_code,dead_age,certificate_no ");
                builder.append(" ,appointment_in_time,appointment_out_time ");
                builder.append(" ,cold_storage.equipment_type_code,cold_storage.process_state_code,cold_storage_equipment.equipment_code ");
                SELECT(builder.toString());
                FROM(" cold_storage ");
                JOIN(" funeral_dead on funeral_dead.business_code = cold_storage.business_code ");
                LEFT_OUTER_JOIN(" cold_storage_equipment on cold_storage_equipment.id = cold_storage.equipment_id and cold_storage_equipment.deleted = 0  ");
                WHERE(" cold_storage.deleted = 0 and funeral_dead.deleted = 0");

                // 业务编码
                if (valid(data.getBusinessCode())) {
                    WHERE(" funeral_dead.business_code like concat('%',#{data.businessCode},'%') ");
                }

                // 逝者姓名
                if (valid(data.getDeadName())) {
                    WHERE(" funeral_dead.dead_name like concat('%',#{data.deadName},'%') ");
                }

                // 预约时间
                if (data.getAppointmentTimeStart() != null) {
                    WHERE(" DATE(appointment_in_time) >= #{data.appointmentTimeStart,jdbcType=DATE} ");
                }

                // 预约时间
                if (data.getAppointmentTimeEnd() != null) {
                    WHERE(" DATE(appointment_in_time) <= #{data.appointmentTimeEnd,jdbcType=DATE} ");
                }

                ORDER_BY(" cold_storage.business_code desc ");
            }
        }.toString();
    }

    /**
     * 礼厅业务查询
     *
     * @param data 查询实体
     * @return
     * @author GuanKui
     * @date 2020/2/24  15:11
     */
    public String listMournCeremony(@Param("data") SearchMournCeremonyData data) {
        return new SQL() {
            {
                StringBuilder builder = new StringBuilder();
                builder.append(" funeral_dead.business_code,dead_name,dead_gender_code,dead_age,certificate_no");
                builder.append(" ,mourn_ceremony.business_type,process_state_code,appointment_in_time,appointment_out_time");
                builder.append(" ,mourn_ceremony_hall.hall_type_code,mourn_ceremony_hall.hall_code,hall_name ");
                SELECT(builder.toString());
                FROM(" mourn_ceremony ");
                JOIN(" funeral_dead on funeral_dead.business_code = mourn_ceremony.business_code ");
                LEFT_OUTER_JOIN(" mourn_ceremony_hall on mourn_ceremony_hall.id = mourn_ceremony.hall_id and mourn_ceremony_hall.deleted = 0  ");
                WHERE(" mourn_ceremony.deleted = 0 and funeral_dead.deleted = 0 ");

                // 业务编码
                if (valid(data.getBusinessCode())) {
                    WHERE(" funeral_dead.business_code like concat('%',#{data.businessCode},'%') ");
                }

                // 逝者姓名
                if (valid(data.getDeadName())) {
                    WHERE(" funeral_dead.dead_name like concat('%',#{data.deadName},'%') ");
                }

                // 业务类型
                if (valid(data.getBusinessType())) {
                    WHERE(" mourn_ceremony.business_type = #{data.businessType}");
                }

                // 预约时间
                if (data.getAppointmentTimeStart() != null) {
                    WHERE(" DATE(appointment_in_time) >= #{data.appointmentTimeStart,jdbcType=DATE} ");
                }

                // 预约时间
                if (data.getAppointmentTimeEnd() != null) {
                    WHERE(" DATE(appointment_in_time) <= #{data.appointmentTimeEnd,jdbcType=DATE} ");
                }

                ORDER_BY(" mourn_ceremony.business_code desc ");
            }
        }.toString();
    }

    /**
     * 火化数据查询
     *
     * @param data 查询实体
     * @return
     * @author GuanKui
     * @date 2020/2/24  10:06
     */
    public String listCremationData(@Param("data") SearchCremationData data) {
        return new SQL() {
            {
                StringBuilder builder = new StringBuilder();
                builder.append(" funeral_dead.business_code,dead_name,dead_gender_code,dead_age,certificate_no");
                builder.append(" ,cremation.furnace_type_code,cremation_furnace.furnace_code ");
                builder.append(" ,as_appointment,appointment_date,appointment_time_segment,cremation.cremation_no");
                SELECT(builder.toString());
                FROM(" cremation ");
                JOIN(" funeral_dead on funeral_dead.business_code = cremation.business_code");
                LEFT_OUTER_JOIN(" cremation_furnace on cremation_furnace.id = cremation.furnace_id and cremation_furnace.deleted = 0  ");
                WHERE(" cremation.deleted = 0 and funeral_dead.deleted = 0 and cremation.as_active = 1 ");

                // 业务编码
                if (valid(data.getBusinessCode())) {
                    WHERE(" funeral_dead.business_code like concat('%',#{data.businessCode},'%') ");
                }

                // 逝者姓名
                if (valid(data.getDeadName())) {
                    WHERE(" funeral_dead.dead_name like concat('%',#{data.deadName},'%') ");
                }

                // 火化预约日期
                if (data.getAppointmentDateStart() != null) {
                    WHERE(" DATE(cremation.appointment_date) >= #{data.appointmentDateStart,jdbcType=DATE} ");
                }

                // 火化预约日期
                if (data.getAppointmentDateEnd() != null) {
                    WHERE(" DATE(cremation.appointment_date) <= #{data.appointmentDateEnd,jdbcType=DATE} ");
                }

                ORDER_BY(" cremation.business_code desc ");
            }
        }.toString();
    }

    /**
     * 火化台账查询
     *
     * @param data 查询实体
     * @return
     * @author GuanKui
     * @date 2020/2/24  10:06
     */
    public String listCremationLedger(@Param("data") SearchCremationLedgerData data, @Param("cremationType") Integer cremationType) {
        return new SQL() {
            {
                StringBuilder builder = new StringBuilder();
                builder.append(" funeral_dead.business_code,dead_name,dead_gender_code,funeral_dead.certificate_no,funeral_dead.certificate_type_code");
                builder.append(" ,death_cause,dead_nation_code,before_death_address,as_local,household_registration_place");
                builder.append(" ,member_name,mobile_phone,other_phone,member_relation");
                builder.append(" ,cremation.as_appointment,cremation.appointment_date,cremation.appointment_time_segment");
                builder.append(" ,cremation.cremation_no");
                SELECT(builder.toString());
                FROM(" cremation ");
                JOIN(" funeral_dead on funeral_dead.business_code = cremation.business_code ");
                LEFT_OUTER_JOIN(" family_member on family_member.business_code = cremation.business_code ");

                WHERE(" cremation.deleted = 0 and cremation.as_active = 1");
                WHERE(" funeral_dead.cremation_type_code = #{cremationType}");

                // 火化预约日期
                if (data.getAppointmentDateStart() != null) {
                    WHERE(" DATE(appointment_date) >= #{data.appointmentDateStart,jdbcType=DATE} ");
                }

                // 火化预约日期
                if (data.getAppointmentDateEnd() != null) {
                    WHERE(" DATE(appointment_date) <= #{data.appointmentDateEnd,jdbcType=DATE} ");
                }

                ORDER_BY(" cremation.business_code desc ");
            }
        }.toString();
    }

    /**
     * 费用查询
     *
     * @param data             查询实体
     * @param invoiceStateCode 作废发票编码
     * @return
     * @author GuanKui
     * @date 2020/2/26  10:03
     */
    public String listChargeQuery(@Param("data") SearchChargeData data, @Param("invoiceStateCode") Integer invoiceStateCode) {
        return new SQL() {
            {
                StringBuilder builder = new StringBuilder();
                builder.append(" funeral_dead.business_code,dead_name");
                builder.append(" ,service_item.item_name,charge_item.item_price,charge_item.item_number,charge_item.item_unit ");
                builder.append(" ,charge_item.item_charge,charge_item.discount_charge,charge_item.benefit_charge,charge_item.reality_charge,charge_item.charge_time,charge_item.as_settled");
                builder.append(" ,settle_batch_no,settle_time,receipt_no,system_user.full_name as settle_user_name,charge_settle.remark");
                builder.append(" ,invoice_code,invoice_no,invoice_title");

                SELECT(builder.toString());
                FROM(" charge_item ");
                JOIN(" funeral_dead on funeral_dead.business_code = charge_item.business_code ");
                JOIN(" service_item on service_item.id = service_item_id ");
                LEFT_OUTER_JOIN(" charge_settle on charge_settle.id = charge_settle_id and charge_settle.deleted = 0  ");
                LEFT_OUTER_JOIN(" charge_invoice on charge_invoice.id = invoice_id and invoice_state_code != #{invoiceStateCode} ");
                LEFT_OUTER_JOIN(" system_user on system_user.id = settle_user_id ");

                WHERE(" charge_item.deleted = 0 and funeral_dead.deleted = 0");

                // 业务编码
                if (valid(data.getBusinessCode())) {
                    WHERE(" funeral_dead.business_code like concat('%',#{data.businessCode},'%') ");
                }

                // 逝者姓名
                if (valid(data.getDeadName())) {
                    WHERE(" funeral_dead.dead_name like concat('%',#{data.deadName},'%') ");
                }

                // 结算时间
                if (data.getSettleTimeStart() != null) {
                    WHERE(" DATE(settle_time) >= #{data.settleTimeStart,jdbcType=DATE} ");
                }

                // 结算时间
                if (data.getSettleTimeEnd() != null) {
                    WHERE(" DATE(settle_time) <= #{data.settleTimeEnd,jdbcType=DATE} ");
                }

                // 结算时间
                if (data.getChargeTimeStart() != null) {
                    WHERE(" DATE(charge_time) >= #{data.chargeTimeStart,jdbcType=DATE} ");
                }

                // 费用时间
                if (data.getChargeTimeEnd() != null) {
                    WHERE(" DATE(charge_time) <= #{data.chargeTimeEnd,jdbcType=DATE} ");
                }

                // 费用时间
                if (valid(data.getItemName())) {
                    WHERE(" service_item.item_name like'%" + data.getItemName() + "%' ");
                }

                ORDER_BY(" charge_item.business_code desc ");
            }
        }.toString();
    }

    /**
     * 惠民减免统计
     *
     * @param data 查询实体
     * @return
     * @author GuanKui
     * @date 2020/2/26  13:34
     */
    public String listHuiMinReduction(@Param("data") SearchHuiMinData data) {
        return new SQL() {
            {
                StringBuilder builder = new StringBuilder();
                builder.append(" funeral_dead.business_code,dead_name,dead_gender_code,dead_age,certificate_no,benefit_district_code");
                builder.append(" ,exemption_type_code,household_registration_place");
                builder.append(" ,(select SUM(discount_charge) from charge_item where charge_item.business_code = funeral_dead.business_code and deleted = 0) as discount_charge");
                builder.append(" ,(select SUM(benefit_charge) from charge_item where charge_item.business_code = funeral_dead.business_code and deleted = 0) as benefit_charge");
                builder.append(" ,cremation.appointment_date");
                SELECT(builder.toString());
                FROM(" funeral_dead");
                LEFT_OUTER_JOIN(" cremation on cremation.business_code = funeral_dead.business_code and cremation.as_active = 1 ");
                WHERE(" funeral_dead.deleted = 0 ");

                // 业务编码
                if (valid(data.getBusinessCode())) {
                    WHERE(" funeral_dead.business_code like concat('%',#{data.businessCode},'%') ");
                }

                // 惠民区域
                if (validAll(data.getBenefitDistrictCode())) {
                    WHERE(" benefit_district_code = #{data.benefitDistrictCode} ");
                }

                // 减免类型
                if (validAll(data.getExemptionTypeCode())) {
                    WHERE(" exemption_type_code = #{data.exemptionTypeCode}");
                }

                // 火化预约日期
                if (data.getAppointmentDateStart() != null) {
                    WHERE(" DATE(cremation.appointment_date) >= #{data.appointmentDateStart,jdbcType=DATE}");
                }

                // 火化预约日期
                if (data.getAppointmentDateEnd() != null) {
                    WHERE(" DATE(cremation.appointment_date) <= #{data.appointmentDateEnd,jdbcType=DATE}");
                }

                ORDER_BY(" funeral_dead.business_code desc ");
            }
        }.toString();
    }

    /**
     * 殡仪馆收入统计
     *
     * @param data             查询实体
     * @param invoiceStateCode 作废发票编码
     * @return
     * @author GuanKui
     * @date 2020/2/26  13:34
     */
    public String listRevenueStatistics(@Param("data") SearchRevenueStatisticsData data, @Param("invoiceStateCode") Integer invoiceStateCode) {

        return new SQL() {
            {
                StringBuilder builder = new StringBuilder();
                builder.append(" charge_settle.id,charge_settle.business_code,dead_name,member_name");
                builder.append(" ,receipt_charge,discount_charge,benefit_charge,settle_charge");
                builder.append(" ,settle_batch_no,settle_time,settle_type_code,receipt_no");
                builder.append(" ,invoice_code,invoice_no,invoice_title");

                SELECT(builder.toString());
                FROM(" charge_settle");
                JOIN(" funeral_business on funeral_business.business_code = charge_settle.business_code ");
                LEFT_OUTER_JOIN(" charge_invoice on charge_invoice.id = invoice_id and invoice_state_code != #{invoiceStateCode} ");
                WHERE(" funeral_business.deleted = 0 and funeral_business.deleted = 0 ");

                // 结算日期
                if (data.getSettleTimeStart() != null) {
                    WHERE("DATE(settle_time) >= #{data.settleTimeStart,jdbcType=DATE} ");
                }

                // 结算日期
                if (data.getSettleTimeEnd() != null) {
                    WHERE(" DATE(settle_time) <= #{data.settleTimeEnd,jdbcType=DATE}");
                }

                ORDER_BY(" funeral_business.business_code desc ");
            }
        }.toString();
    }

    /**
     * 按照结算主键获取付款方式统计
     *
     * @param chargeSettleIds 结算费用集合
     * @return
     * @author LiCongLu
     * @date 2020-03-07 17:47
     */
    public String listRevenueStatisticsPayByChargeSettleIds(@Param("chargeSettleIds") ArrayList<Integer> chargeSettleIds) {

        return new SQL() {
            {
                StringBuilder builder = new StringBuilder();
                builder.append(" charge_settle_id,pay_type_code,SUM(pay_charge) as pay_charge ");
                SELECT(builder.toString());
                FROM(" charge_settle_pay ");
                WHERE(" deleted = 0 ");

                // 主键
                String idString = StringUtils.arrayToDelimitedString(chargeSettleIds.toArray(new Integer[]{}), ",");
                WHERE("  charge_settle_id in (" + idString + ")");

                GROUP_BY(" charge_settle_id,pay_type_code ");
            }
        }.toString();
    }

    /**
     * 操作日志查询
     *
     * @param data 查询实体
     * @return
     * @author LiCongLu
     * @date 2020-04-20 15:32
     */
    public String listSystemOperationLog(@Param("data") SearchOperationLogData data) {
        return new SQL() {
            {
                StringBuilder builder = new StringBuilder();
                builder.append(" CONCAT_WS('/',system_user.full_name,system_user.job_no) as full_name_job_no");
                builder.append(" ,system_operation_log.gmt_create as operation_time,log_content,operation_type,business_type");
                builder.append(" ,funeral_dead.business_code,dead_name");
                SELECT(builder.toString());
                FROM(" system_operation_log ");
                LEFT_OUTER_JOIN(" funeral_dead on funeral_dead.business_code = system_operation_log.business_code ");
                LEFT_OUTER_JOIN(" system_user on system_user.id = system_operation_log.create_user_id ");

                WHERE(" system_operation_log.deleted = 0 ");

                // 业务编码
                if (valid(data.getBusinessCode())) {
                    WHERE(" system_operation_log.business_code like concat('%',#{data.businessCode},'%') ");
                }

                // 员工姓名/工号
                if (valid(data.getFullNameJobNo())) {
                    WHERE(" ( system_user.full_name like concat('%',#{data.fullNameJobNo},'%') or system_user.job_no like concat('%',#{data.fullNameJobNo},'%') ) ");
                }

                // 日志内容
                if (valid(data.getLogContent())) {
                    WHERE(" log_content like concat('%',#{data.logContent},'%') ");
                }

                // 操作时间
                if (data.getCreateTimeStart() != null) {
                    WHERE(" DATE_FORMAT(system_operation_log.gmt_create,'%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(#{data.createTimeStart,jdbcType=TIMESTAMP},'%Y-%m-%d %H:%i:%s') ");
                }

                // 操作时间
                if (data.getCreateTimeEnd() != null) {
                    WHERE(" DATE_FORMAT(system_operation_log.gmt_create,'%Y-%m-%d %H:%i:%s') <= DATE_FORMAT(#{data.createTimeEnd,jdbcType=TIMESTAMP},'%Y-%m-%d %H:%i:%s') ");
                }

                ORDER_BY(" system_operation_log.gmt_create desc ");
            }
        }.toString();
    }
}
